package com.project.dao.impl;

import com.project.bean.CutPageBean;
import com.project.bean.UserBean;
import com.project.dao.IUserDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@Repository
public class UserDaoImpl extends BaseDao implements IUserDao {

    @Override
    public void add(UserBean userBean) {
        this.setConnection();
        try {
            ps = con.prepareCall("INSERT INTO m_user(m_userName,m_sex,m_money,m_birthday)" +
                    "VALUES(?,?,?,?)");

            ps.setObject(1, userBean.getName());
            ps.setObject(2, userBean.getSex());
            ps.setObject(3, userBean.getMoney());
            ps.setObject(4, userBean.getBirthday());

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public CutPageBean<UserBean> findByItem(Integer pageNO, String name, LocalDate startDate, LocalDate endDate) {
        CutPageBean<UserBean> cut = new CutPageBean<>();
        this.setConnection();
        String sql = "select * from m_user where 1=1 ";
        String dynaSql = "";
        if (name != null && name.length() != 0) {
            dynaSql += "AND m_userName LIKE '%" + name + "%' ";
        }
        if (startDate != null) {
            dynaSql += "AND m_birthday >= " + "'" + startDate + "'" + " ";
        }
        if (endDate != null) {
            dynaSql += "AND m_birthday <= " + "'" + endDate + "'" + " ";
        }
        try {
            ps = con.prepareStatement(sql + dynaSql + " LIMIT ?,?");
            ps.setObject(1, (pageNO - 1) * CutPageBean.PAGESIZE);
            ps.setObject(2, CutPageBean.PAGESIZE);
            re = ps.executeQuery();
            while (re.next()) {
                cut.getList().add(this.getUser());
            }
            ps = con.prepareStatement("select count(*) c from m_user where 1=1 " + dynaSql);
            re = ps.executeQuery();
            if (re.next()) {
                cut.setCount(re.getInt("c"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return cut;
    }

    public UserBean getUser() {
        UserBean user = new UserBean();
        try {
            user.setId(re.getInt("pk_id"));
            user.setName(re.getString("m_userName"));
            user.setSex(re.getString("m_sex"));
            String bir = re.getString("m_birthday");
            user.setBirthday(bir != null ? LocalDate.parse(bir) : null);
            user.setMoney(re.getInt("m_money"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }
}